﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NTTDATA.BEENChina.ResultCertification.Config;
using NTTDATA.BEENChina.ResultCertification.model;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class CategoryConfigAccess:ICagetoryConfig
    {
        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"];
            }
        }

        /// <summary>
        /// 2013 11 05 add caid
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public CategoryConfig GetCagetoryConfig(string categoryId)
        {
            string sql = "  SELECT [CategoryID],[CategoryName],[CategoryType],[ResultFormat],[SplitFormat]"+
                        "           ,[DataFormat],[Columns],[CaID] " +
                        "   FROM [CategoryMaster]  "+
                        "   WHERE [CategoryID] = @CategoryID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryID",  categoryId)
                };

            CategoryConfig categoryConfig = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (categoryConfig == null)
                            categoryConfig = new CategoryConfig();

                        categoryConfig.CategoryId = reader["CategoryID"].ToString();
                        categoryConfig.CategoryName = reader["CategoryName"].ToString();
                        categoryConfig.CategoryType = (EnumCategoryType)(int.Parse( reader["CategoryType"].ToString() ));
                        categoryConfig.SplitFormat = reader["SplitFormat"].ToString();
                        categoryConfig.DataFormat =  reader["DataFormat"].ToString();
                        categoryConfig.Columns = reader["Columns"] == DBNull.Value ? 0 : int.Parse(reader["Columns"].ToString());

                        categoryConfig.ResultFormate = reader["ResultFormat"].ToString();
                        categoryConfig.CaId = reader["CaID"].ToString();
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("CategoryConfigAccess.GetCategoryConfigBaseInfo", ex);
            }

            return categoryConfig;
        }

        public int SetCagetoryConfig(CategoryConfig categoryConfig)
        {
            if (categoryConfig == null)
                return -1;

            string sql =   "   UPDATE [CategoryMaster] " +
                           "   SET  [CategoryName] = @CategoryName " +
                           "       ,[CategoryType] = @CategoryType " +
                           //"       ,[ResultFormat] = @ResultFormat " +
                           "       ,[SplitFormat] = @SplitFormat " +
                           "       ,[DataFormat] = @DataFormat " +
                           "   WHERE [CategoryID] = @CategoryID ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryName",  categoryConfig.CategoryName),
                    new SqlParameter("@CategoryType",  categoryConfig.CategoryType),
                    new SqlParameter("@SplitFormat",  categoryConfig.SplitFormat),
                    new SqlParameter("@DataFormat",  categoryConfig.DataFormat),
                    new SqlParameter("@CategoryID",  categoryConfig.CategoryId)
                    
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);
        }

        public int RemoveCategoryVerfities(string categoryId)
        {
            string sql = "DELETE FROM  [CategoryVerfityItem]     WHERE [CategoryID] = @CategoryID ";

            SqlParameter[] sqlParameters = 
                { 
                    new SqlParameter("@CategoryID",  categoryId)
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);

        }

        public int AddCategoryVerfities(string categoryId, IEnumerable<VerfityItem> verfityItems)
        {
            if (verfityItems == null)
                return -1;

            int effactRows = 0;
            foreach (var verfityItem in verfityItems)
            {
               effactRows += AddCategoryVerfitie(categoryId, verfityItem.VerfityItemId);
            }

            return effactRows;
        }

        public IEnumerable<VerfityItem> GetCategoryVerfitys(string categoryId)
        {
            string sql = "SELECT [CategoryID] ,[CategoryVerfityItem].[VerfityItemID], [VerfityItemName] " +
                        " FROM  [CategoryVerfityItem] INNER JOIN VerfityItem " +
                        " ON [CategoryVerfityItem].[VerfityItemID] =VerfityItem.VerfityItemID " +
                        " WHERE [CategoryID] = @CategoryID ";
            SqlParameter[] sqlParameters = 
                { 
                    new SqlParameter("@CategoryID",  categoryId)
                };

            List<VerfityItem> verfitys = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (verfitys == null)
                            verfitys = new List<VerfityItem>();

                        VerfityItem verfityItem = new VerfityItem();                        
                        verfityItem.VerfityItemId = reader["VerfityItemID"].ToString();
                        verfityItem.VerfityItemName = reader["VerfityItemName"].ToString();
                        verfitys.Add(verfityItem);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("CategoryConfigAccess.GetCagetoryConfigs", ex);
            }

            return verfitys;
        }


        private List<VerfityItem> GetCategoryVerfitys(EnumCategoryType categoryType)
        {
            string sql = "SELECT [CategoryID] ,[CategoryVerfityItem].[VerfityItemID], [VerfityItemName] " +
                        " FROM  [CategoryVerfityItem] INNER JOIN VerfityItem " +
                        " ON [CategoryVerfityItem].[VerfityItemID] =VerfityItem.VerfityItemID " +
                        "   WHERE [CategoryType] = @CategoryType ";
            SqlParameter[] sqlParameters = 
                { 
                     new SqlParameter("@CategoryType",  (int)categoryType)
                };

            List<VerfityItem> verfitys = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (verfitys == null)
                            verfitys = new List<VerfityItem>();

                        VerfityItem verfityItem = new VerfityItem();
                        verfityItem.CategoryId = reader["CategoryID"].ToString();
                        verfityItem.VerfityItemId = reader["VerfityItemID"].ToString();
                        verfityItem.VerfityItemName = reader["VerfityItemName"].ToString();
                        verfitys.Add(verfityItem);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("CategoryConfigAccess.GetCategoryVerfitys", ex);
            }

            return verfitys;
        }

        private int AddCategoryVerfitie(string categoryId, string verfityItemID)
        {
            string sql = "INSERT INTO [CategoryVerfityItem] " +
                         " ([CategoryID]	,[VerfityItemID]) " +
                         " VALUES " +
                         " (@CategoryID	,@VerfityItemID) ";

            SqlParameter[] sqlParameters = 
                { 
                    new SqlParameter("@CategoryID",  categoryId),
                    new SqlParameter("@VerfityItemID",  verfityItemID)
                };

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);

        }

        public IEnumerable<CategoryConfig> GetCagetoryConfigs(EnumCategoryType categoryType)
        {
            List<VerfityItem> verfityItems = GetCategoryVerfitys(  categoryType) ;

            string sql = "  SELECT [CategoryID],[CategoryName],[CategoryType],[ResultFormat],[SplitFormat]" +
                        "           ,[DataFormat] FROM [CategoryMaster]  " +
                        "   WHERE [CategoryType] = @CategoryType ";

            SqlParameter[] sqlParameters = 
                {
                    new SqlParameter("@CategoryType",  (int)categoryType)
                };

            List<CategoryConfig> categoryConfigs = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        if (categoryConfigs == null)
                            categoryConfigs = new List<CategoryConfig>();

                        CategoryConfig categoryConfig = new CategoryConfig();
                        categoryConfig.CategoryId = reader["CategoryID"].ToString();
                        categoryConfig.CategoryName = reader["CategoryName"].ToString();
                        categoryConfig.CategoryType = (EnumCategoryType)(int.Parse(reader["CategoryType"].ToString()));
                        categoryConfig.SplitFormat = reader["SplitFormat"].ToString();
                        categoryConfig.DataFormat = reader["DataFormat"].ToString();

                        if (verfityItems != null && verfityItems.Any())
                        categoryConfig.VerfityItems = verfityItems.Where(t => t.CategoryId.Equals(categoryConfig.CategoryId)).ToList();

                        categoryConfigs.Add(categoryConfig);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("CategoryConfigAccess.GetCagetoryConfigs", ex);
            }

            return categoryConfigs;
        }
    }
}